package com.studorm.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;

import org.apache.struts2.ServletActionContext;

import com.opensymphony.xwork2.ActionSupport;
import com.studorm.Connection_mysql;
import com.studorm.vo.Utilities;

/* －－－－－－－－－－－－－－－－－
 * 		水电费操作类
 * －－－－－－－－－－－－－－－－－
 * 对水电费的操作均写在此
 * 前台请求后，带有get函数的变量
 * 均被转成JSON格式传到前台
 * 					by Huang
 */
public class UtilitiesAction extends ActionSupport {

	/**
	 * 
	 */
	private static final long serialVersionUID = -3697921271744988026L;

	// 此变量为存放你要读取的表格数据，前台请求后会自动生成JSON格式发送到客户端
	private List<Utilities> tableRow = new ArrayList<Utilities>();
	private int amountOfRow = 0;
	public List<Utilities> getTableRow() {
		return tableRow;
	}
	public void setTableRow(List<Utilities> tableRow) {
		this.tableRow = tableRow;
	}
	public String loadUtilities() throws Exception{
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String dormBdName = request.getParameter("dormBdName"); 
		String dormNu = request.getParameter("dormNu");
		String year = request.getParameter("year");
		String rowEachP_t = request.getParameter("rowEachP");//每页行数
		String nuOfP_t = request.getParameter("nuOfP");//要获取第几页的数据
		if(dormBdName == null || dormNu == null || year == null){
			return ERROR;
		}

		int rowEachP = 10; //每页行数
		int nuOfP = 1;//当前是第几页
		if(rowEachP_t != null){
			rowEachP = Integer.parseInt(rowEachP_t);
		}
		if(nuOfP_t != null){
			nuOfP = Integer.parseInt(nuOfP_t);
		}

		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps;
		ResultSet res;
		String sql;

		/* ------------------------
		 * 	此处得到总数据条数，用来计算页数
		 * ------------------------
		 */
		if(dormNu.equals("全部")){
			sql = "SELECT COUNT(*) FROM studormcms.dormutilities WHERE dormBdName=? AND year=?";
			ps = conn.prepareStatement(sql);

		}else{
			sql = "SELECT COUNT(*) FROM studormcms.dormutilities WHERE dormBdName=? AND year=? AND dormNu=?";
			ps = conn.prepareStatement(sql);
			ps.setString(3, dormNu);
		}
		ps.setString(1, dormBdName);
		ps.setString(2, year);
		res = ps.executeQuery();
		while(res.next()){
			amountOfRow = res.getInt(1);
		}
		res.close();
		ps.close();

		/* －－－－－－－－－－－－－－－－－－－
		 * 此处从数据库读取前台请求的某页的数据
		 * －－－－－－－－－－－－－－－－－－－
		 */
		if(dormNu.equals("全部")){
			sql = "SELECT id, dormBdName, dormNu, year, month, electricity, electricityConsumption, ispayE, water, waterConsumption, ispayW FROM studormcms.dormutilities WHERE dormBdName=? AND year=? LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(3, (nuOfP-1) * rowEachP);
			ps.setInt(4, rowEachP);
		}else{
			sql = "SELECT id, dormBdName, dormNu, year, month, electricity, electricityConsumption, ispayE, water, waterConsumption, ispayW FROM studormcms.dormutilities WHERE dormBdName=? AND year=? AND dormNu=? LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setString(3, dormNu);
			ps.setInt(4, (nuOfP-1) * rowEachP);
			ps.setInt(5, rowEachP);
		}
		ps.setString(1, dormBdName);
		ps.setString(2, year);

		res = ps.executeQuery();
		while(res.next()){
			Utilities u = new Utilities();
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormNu(res.getString("dormNu"));
			u.setElectricity(res.getDouble("electricity"));
			u.setId(res.getInt("id"));
			u.setIspayE(res.getInt("ispayE"));
			u.setIspayW(res.getInt("ispayW"));
			u.setMonth(res.getString("month"));
			u.setWater(res.getDouble("water"));
			u.setYear(res.getString("year"));
			u.setElectricityConsumption(res.getDouble("electricityConsumption"));
			u.setWaterConsumption(res.getDouble("waterConsumption"));
			
			tableRow.add(u);
		}
		res.close();
		ps.close();
		conn.close();

		return SUCCESS;
	}
	public int getAmountOfRow() {
		return amountOfRow;
	}
	public void setAmountOfRow(int amountOfRow) {
		this.amountOfRow = amountOfRow;
	}

	
	/* ---------------------------
	 * 		删除水电费项 ver.11.23
	 * ---------------------------
	 * 
	 * 从前台ajax请求获得delId参数
	 * 此参数为水电费记录的ID，通过此ID
	 * 用SQL语句删除数据库中的记录
	 * 					by Huang
	 */
	public String delUtilities() throws Exception{
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String uId = request.getParameter("delId");
		int id = 0;
		if(uId != null){
			id = Integer.parseInt(uId);
		}else{
			return NONE;
		}
		Connection conn = Connection_mysql.getConnection();
		String sql = "DELETE FROM utilities WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res == 1){
			return SUCCESS;
		}else{
			return NONE;
		}
	}
	
	/* --------------------------------
	 * 		添加水电费信息 ver.11.23
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	public String addUtilities() throws Exception{
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String dormBdName = request.getParameter("dormBdName");
		String dormNu = request.getParameter("dormNu");
		String yearMonth = request.getParameter("yearMonth");
		String water = request.getParameter("water");
		String electricity = request.getParameter("electricity");
		String waterC = request.getParameter("waterC");
		String electricityC = request.getParameter("electricityC");

		Pattern patternNu = Pattern.compile("\\d{3}");// 匹配3位整数
		Pattern patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*"); // 匹配小数或整数
		Pattern patternYM = Pattern.compile("[1-2]\\d*-[0-1]{0,1}[0-9]");// 匹配 ????-??的年月格式
		
		if(dormBdName.isEmpty() || dormBdName.equals("请选择")){
			return NONE;
		}
		if(dormNu == null || dormNu.isEmpty() || !patternNu.matcher(dormNu).matches()){
			return NONE;
		}
		if(yearMonth == null || yearMonth.isEmpty() || !patternYM.matcher(yearMonth).matches()){
			return NONE;
		}
		if(water == null || water.isEmpty() || !patternDeci.matcher(water).matches()){
			return NONE;
		}
		if(electricity == null || electricity.isEmpty() || !patternDeci.matcher(electricity).matches()){
			return NONE;
		}
		if(waterC == null || waterC.isEmpty() || !patternDeci.matcher(waterC).matches()){
			return NONE;
		}
		if(electricityC == null || electricityC.isEmpty() || !patternDeci.matcher(electricityC).matches()){
			return NONE;
		}

		Connection conn = Connection_mysql.getConnection();
		String sql = "INSERT INTO utilities (dorm,date,electricityConsumption,electricity,waterConsumption,water,ispayW,ispayE) VALUES ((SELECT id FROM dorminf where dormBdName=? and dormNu=?),?,?,?,?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, dormBdName);
		ps.setString(2, dormNu);
		ps.setString(3, yearMonth+"-06");
		ps.setDouble(4, Double.parseDouble(electricityC));
		ps.setDouble(5, Double.parseDouble(electricity));
		ps.setDouble(6, Double.parseDouble(waterC));
		ps.setDouble(7, Double.parseDouble(water));  // change at 11.24 bug
		ps.setInt(8, 0);
		ps.setInt(9, 0);
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	
	/* --------------------------------
	 * 		编辑水电费信息 ver.11.24
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	public String editUtilities() throws Exception{
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String yearMonth = request.getParameter("yearMonth");
		String water = request.getParameter("water");
		String electricity = request.getParameter("electricity");
		String waterC = request.getParameter("waterC");
		String electricityC = request.getParameter("electricityC");
		String id = request.getParameter("uid");

		Pattern patternNu = Pattern.compile("\\d*");
		Pattern patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*"); // 匹配小数或整数
		Pattern patternYM = Pattern.compile("[1-2]\\d*-[0-1]{0,1}[0-9]");// 匹配 ????-??的年月格式
		
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}

		if(yearMonth == null || yearMonth.isEmpty() || !patternYM.matcher(yearMonth).matches()){
			return NONE;
		}
		if(water == null || water.isEmpty() || !patternDeci.matcher(water).matches()){
			return NONE;
		}
		if(electricity == null || electricity.isEmpty() || !patternDeci.matcher(electricity).matches()){
			return NONE;
		}
		if(waterC == null || waterC.isEmpty() || !patternDeci.matcher(waterC).matches()){
			return NONE;
		}
		if(electricityC == null || electricityC.isEmpty() || !patternDeci.matcher(electricityC).matches()){
			return NONE;
		}

		Connection conn = Connection_mysql.getConnection();
		String sql = "UPDATE utilities SET date=?,electricityConsumption=?,electricity=?,waterConsumption=?,water=? WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(6, Integer.parseInt(id));
		ps.setString(1, yearMonth+"-06");
		ps.setDouble(2, Double.parseDouble(electricityC));
		ps.setDouble(3, Double.parseDouble(electricity));
		ps.setDouble(4, Double.parseDouble(waterC));
		ps.setDouble(5, Double.parseDouble(water));  // change at 11.24 bug

		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	
	/* -----------------------------
	 * 	只读取一行指定水电费记录 ver.11.24
	 * -----------------------------
	 * 
	 */
	public String loadAUtilities() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String utilId = request.getParameter("editId");
		Pattern patternNu = Pattern.compile("\\d*");
		if(utilId == null || utilId.isEmpty() || !patternNu.matcher(utilId).matches()){
			return NONE;
		}
		String sql = "SELECT id, dormBdName, dormNu, year, month, electricity, electricityConsumption, water, waterConsumption FROM studormcms.dormutilities WHERE id = ?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, Integer.parseInt(utilId));
		ResultSet res = ps.executeQuery();
		while(res.next()){
			Utilities u = new Utilities();
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormNu(res.getString("dormNu"));
			u.setElectricity(res.getDouble("electricity"));
			u.setId(res.getInt("id"));
			u.setMonth(String.format("%02d", Integer.parseInt(res.getString("month"))));//格式化月份为两位数，不足前面补0
			u.setWater(res.getDouble("water"));
			u.setYear(res.getString("year"));
			u.setElectricityConsumption(res.getDouble("electricityConsumption"));
			u.setWaterConsumption(res.getDouble("waterConsumption"));
			
			tableRow.add(u);
		}
		res.close();
		ps.close();
		conn.close();
		
		return SUCCESS;
	}
	
	/* -----------------------------
	 * 		修改水费是否缴纳 ver.11.26
	 * -----------------------------
	 * 
	 */
	public String alterPayW() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String id = request.getParameter("alterId");
		String status = request.getParameter("status");
		Pattern patternNu = Pattern.compile("\\d*");
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}
		String sql = "UPDATE utilities SET ispayW = ? WHERE id=?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(2, Integer.parseInt(id));
		if(status.equals("未交")){
			ps.setInt(1, 1);
		}else if(status.equals("已交")){
			ps.setInt(1, 0);
		}else{
			return NONE;
		}
		
		int res = ps.executeUpdate();
		ps.close();
		conn.close();
		if(res > 1){
			return SUCCESS;
		}else{
			return NONE;
		}
	}
	
	
	/* -----------------------------
	 * 		修改电费是否缴纳 ver.11.26
	 * -----------------------------
	 * 
	 */
	public String alterPayE() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String id = request.getParameter("alterId");
		String status = request.getParameter("status");
		Pattern patternNu = Pattern.compile("\\d*");
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}
		String sql = "UPDATE utilities SET ispayE = ? WHERE id=?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(2, Integer.parseInt(id));
		if(status.equals("未交")){
			ps.setInt(1, 1);
		}else if(status.equals("已交")){
			ps.setInt(1, 0);
		}else{
			return NONE;
		}
		
		int res = ps.executeUpdate();
		ps.close();
		conn.close();
		if(res > 1){
			return SUCCESS;
		}else{
			return NONE;
		}
	}
}
